# _*_ coding:utf-8 _*_
# @Time  : 2022.06.10
# @Author: zizlee
import datetime
import json
import math
import pathlib
import uuid
import time
import random
import shutil
from typing import List
from fastapi import APIRouter, Header, Body, Query
from fastapi.encoders import jsonable_encoder
from pydantic import BaseModel, ValidationError, validator, constr, conint, datetime_parse

import openpyxl
from openpyxl import styles as openpyxl_styles
from openpyxl.drawing.image import Image as ODImage
from openpyxl.drawing.xdr import XDRPositiveSize2D
from openpyxl.drawing.spreadsheet_drawing import AnchorMarker, OneCellAnchor
from openpyxl.worksheet.page import PageMargins
from openpyxl.utils.units import pixels_to_EMU

from reportlab.platypus import SimpleDocTemplate, Paragraph, Spacer, Image, Table, TableStyle
from reportlab.lib import colors
from reportlab.lib.styles import ParagraphStyle
from reportlab.lib.pagesizes import A4
from reportlab.lib.units import cm

from db import FAConnection
from hutool import security
from global_constant import VARIETY
from v1_all_api import constant
from v1_all_api.all_response import AllResponse
from v1_all_api.all_utils import datalib_utils, datetime_utils, file_utils
from configs import FILE_STORAGE, WEB_HOST


PAGE_WIDTH, PAGE_HEIGHT = A4

daily_report_api = APIRouter()


class ReportAuthor(BaseModel):
    name: str
    certificate1: str
    certificate2: str


class IndexItem(BaseModel):
    index_name: str
    value: str
    diff: str
    is_active: bool


class ReportIndexItem(BaseModel):
    category_name: str
    indexes: List[IndexItem]


class ReportMessageItem(BaseModel):
    category_name: str
    message: str


class ExportContentItem(BaseModel):
    report_title: str
    report_date: str
    author: ReportAuthor
    note: str
    index_categories: List[ReportIndexItem]
    msg_categories: List[ReportMessageItem]
    variety_en: str


class ReportExcelFileHandler(object):
    TITLE_FONT_SIZE = 20
    DATE_FONT_SIZE = 14
    AUTHOR_FONT_SIZE = 8
    TABLE_HEADER_FONT_SIZE = 12
    TABLE_CONTENT_FONT_SIZE = 9
    NOTE_FONT_SIZE = 7

    DEFAULT_FONT_NAME = '微软雅黑'
    AUTHOR_FONT_NAME = '等线'
    TABLE_HEADER_FONT_NAME = '等线'
    TABLE_CONTENT_FONT_NAME = '微软雅黑'
    NOTE_FONT_NAME = '等线'
    # DEFAULT_FONT_NAME = '宋体'
    # AUTHOR_FONT_NAME = '宋体'
    # TABLE_HEADER_FONT_NAME = '宋体'  # fontSize=11
    # TABLE_CONTENT_FONT_NAME = '宋体'
    # NOTE_FONT_NAME = '宋体'

    def __init__(self, report_basic, indexes_categories, msg_categories):
        self.report_basic = report_basic
        self.indexes_categories = indexes_categories
        self.msg_categories = msg_categories

    @staticmethod
    def cell_font(size=11, color='000000', bold=False, name=None):
        return openpyxl_styles.Font(size=size, color=color, bold=bold, name=name)

    @staticmethod
    def cell_alignment(horizontal=None, vertical=None, wrap_text=None):
        return openpyxl_styles.Alignment(horizontal=horizontal, vertical=vertical, wrap_text=wrap_text)

    @staticmethod
    def cell_forward_color(color="ffffff"):
        # patternType='solid' # 纯色填充 bgColor背景色,fgColor 前景色
        return openpyxl_styles.PatternFill(patternType='solid', fgColor=color)

    @staticmethod
    def cell_border(left=None, top=None, right=None, bottom=None):
        # 边框样式，可选dashDot、dashDotDot、dashed、dotted、double、hair、medium、mediumDashDot、
        # mediumDashDotDot、mediumDashed、slantDashDot、thick、thin
        side = openpyxl_styles.Side
        colors = openpyxl_styles.colors
        return openpyxl_styles.Border(top=side(style=top, color=colors.BLACK),
                                      left=side(style=left, color=colors.BLACK),
                                      right=side(style=right, color=colors.BLACK),
                                      bottom=side(style=bottom, color=colors.BLACK))

    @staticmethod
    def get_diff_value_text_color(value):
        if str(value) in ['NAN', 'ERR', '0']:
            return str(value), '000000'
        else:
            return (f'{value}↑', 'ff0000') if float(value) > 0 else (f'{value}↓', '0000ff')

    @staticmethod
    def get_watermark():  # 添加水印
        watermark_path = pathlib.Path(FILE_STORAGE).joinpath('AppImages/dailyReport/watermark.png')
        watermark = ODImage(watermark_path)
        watermark.width, watermark.height = (300, 660)
        size = XDRPositiveSize2D(pixels_to_EMU(watermark.height), pixels_to_EMU(watermark.width))
        marker = AnchorMarker(colOff=pixels_to_EMU(10), rowOff=pixels_to_EMU(0), row=10, col=0)
        watermark.anchor = OneCellAnchor(_from=marker, ext=size)
        return watermark

    @staticmethod
    def get_line_height(index, message):
        total = 51 if index <= 1 else 55
        if len(message) < total:
            return 14.25
        line_count = 1
        char_count = 0
        for c in message:
            if c == '\n':
                char_count = 0
                line_count = line_count + 1
                continue
            char_count = char_count + 1
            if char_count >= total:
                line_count = line_count + 1
                char_count = 0
        return math.ceil(line_count * 14.25)

    def write_indexes_values(self, sheet, start_row):
        category_row = start_row - 1  # 从第四行开始写,因为一开始+1，此处=3
        for category_item in self.indexes_categories:
            category_row += 1
            c_start_row = category_row
            sheet[f'A{category_row}'] = category_item['category_name']
            sheet[f'A{category_row}'].font = self.cell_font(size=self.TABLE_CONTENT_FONT_SIZE, 
                                                            name=self.TABLE_CONTENT_FONT_NAME)
            sheet[f'A{category_row}'].alignment = self.cell_alignment(horizontal='center', vertical='center')
            sheet[f'A{category_row}'].border = self.cell_border(right='medium', bottom='medium')
            for i, index_item in enumerate(category_item['indexes']):
                if not index_item['is_active']:
                    continue
                if i > 0 and i % 2 == 0:
                    category_row += 1
                if i % 2 == 0:
                    sheet[f'B{category_row}'] = index_item['index_name']
                    sheet[f'B{category_row}'].font = self.cell_font(size=self.TABLE_CONTENT_FONT_SIZE,
                                                                    name=self.TABLE_CONTENT_FONT_NAME)
                    sheet[f'B{category_row}'].alignment = self.cell_alignment(vertical='center')
                    sheet[f'C{category_row}'] = index_item['value']
                    sheet[f'C{category_row}'].font = self.cell_font(size=self.TABLE_CONTENT_FONT_SIZE, bold=True,
                                                                    name=self.TABLE_CONTENT_FONT_NAME)
                    sheet[f'C{category_row}'].alignment = self.cell_alignment(horizontal='right', vertical='center')
                    diff_value, diff_color = self.get_diff_value_text_color(index_item['diff'])
                    sheet[f'D{category_row}'] = diff_value
                    sheet[f'D{category_row}'].font = self.cell_font(size=self.TABLE_CONTENT_FONT_SIZE, bold=True,
                                                                    color=diff_color, name=self.TABLE_CONTENT_FONT_NAME)
                    sheet[f'D{category_row}'].alignment = self.cell_alignment(horizontal='right', vertical='center')
                    sheet[f'D{category_row}'].border = self.cell_border(right='dashed')
                else:
                    sheet[f'E{category_row}'] = index_item['index_name']
                    sheet[f'E{category_row}'].font = self.cell_font(size=self.TABLE_CONTENT_FONT_SIZE,
                                                                    name=self.TABLE_CONTENT_FONT_NAME)
                    sheet[f'E{category_row}'].alignment = self.cell_alignment(vertical='center')
                    sheet[f'F{category_row}'] = index_item['value']
                    sheet[f'F{category_row}'].font = self.cell_font(size=self.TABLE_CONTENT_FONT_SIZE, bold=True,
                                                                    name=self.TABLE_CONTENT_FONT_NAME)
                    sheet[f'F{category_row}'].alignment = self.cell_alignment(horizontal='right', vertical='center')
                    diff_value, diff_color = self.get_diff_value_text_color(index_item['diff'])
                    sheet[f'G{category_row}'] = diff_value
                    sheet[f'G{category_row}'].font = self.cell_font(size=self.TABLE_CONTENT_FONT_SIZE, bold=True,
                                                                    color=diff_color, name=self.TABLE_CONTENT_FONT_NAME)
                    sheet[f'G{category_row}'].alignment = self.cell_alignment(horizontal='right', vertical='center')

            # 下边框
            for col in ['B', 'C', 'D', 'E', 'F', 'G']:
                sheet[f'{col}{category_row}'].border = self.cell_border(bottom='medium')
                if col == 'D':  # 右侧的点分割线
                    sheet[f'{col}{category_row}'].border = self.cell_border(bottom='medium', right='dashed')
            # 合并类别名称单元格
            sheet.merge_cells(f'A{c_start_row}:A{category_row}')

        for i, category_item in enumerate(self.msg_categories):
            category_row += 1
            sheet[f'A{category_row}'] = category_item['category_name']
            sheet[f'A{category_row}'].font = self.cell_font(size=self.TABLE_CONTENT_FONT_SIZE,
                                                            name=self.TABLE_CONTENT_FONT_NAME)
            sheet[f'A{category_row}'].alignment = self.cell_alignment(horizontal='center', vertical='center')
            sheet[f'A{category_row}'].border = self.cell_border(top='medium', right='medium', bottom='medium')
            sheet[f'B{category_row}'] = category_item['message']  # 写入文字
            sheet[f'B{category_row}'].alignment = self.cell_alignment(horizontal='left', vertical='top', wrap_text=True)
            sheet[f'B{category_row}'].border = self.cell_border(bottom='medium')
            sheet[f'B{category_row}'].font = self.cell_font(size=self.TABLE_CONTENT_FONT_SIZE, name=self.TABLE_CONTENT_FONT_NAME)
            line_height = self.get_line_height(i, category_item['message'])
            if i == 0:
                sheet.merge_cells(f'B{category_row}:F{category_row}')  # 下边框
                # 贴二维码1
                qrcode1_path = pathlib.Path(FILE_STORAGE).joinpath('AppImages/dailyReport/yjyqrcode.png')
                qrcode1 = ODImage(qrcode1_path)
                qrcode1.width, qrcode1.height = (90, 65)
                # 计算出图片在excel中位置，是图片的大小
                size = XDRPositiveSize2D(pixels_to_EMU(qrcode1.height), pixels_to_EMU(qrcode1.width))
                # 相对于单元格内的(x,y,top,left)偏移量,行从0计数，此处-1
                marker = AnchorMarker(colOff=pixels_to_EMU(0), rowOff=pixels_to_EMU(5), row=category_row - 1, col=6)
                qrcode1.anchor = OneCellAnchor(_from=marker, ext=size)
                sheet.add_image(qrcode1)

                sheet[f'G{category_row}'].alignment = self.cell_alignment(horizontal='center', vertical='top')
                sheet[f'G{category_row}'].border = self.cell_border(bottom='medium')
                sheet.row_dimensions[category_row].height = 90 if line_height < 90 else line_height  # 行高
            elif i == 1:
                sheet.merge_cells(f'B{category_row}:F{category_row}')  # 下边框
                # 贴二维码2
                qrcode2_path = pathlib.Path(FILE_STORAGE).joinpath('AppImages/dailyReport/wxkfqrcode.png')
                qrcode2 = ODImage(qrcode2_path)
                qrcode2.width, qrcode2.height = (90, 65)
                # 计算出图片在excel中位置，是图片的大小
                size2 = XDRPositiveSize2D(pixels_to_EMU(qrcode2.height), pixels_to_EMU(qrcode2.width))
                # 相对于单元格内的(x,y,top,left)偏移量,行从0计数，此处-1
                marker = AnchorMarker(colOff=pixels_to_EMU(0), rowOff=pixels_to_EMU(5), row=category_row - 1, col=6)
                qrcode2.anchor = OneCellAnchor(_from=marker, ext=size2)
                sheet.add_image(qrcode2)

                sheet[f'G{category_row}'].alignment = self.cell_alignment(horizontal='center', vertical='top')
                sheet[f'G{category_row}'].border = self.cell_border(bottom='medium')
                sheet.row_dimensions[category_row].height = 90 if line_height < 90 else line_height
            else:
                sheet.merge_cells(f'B{category_row}:G{category_row}')
                sheet.row_dimensions[category_row].height = line_height

        return category_row + 1

    def create_excel_file(self):
        work_book = openpyxl.Workbook()
        sheet = work_book.active
        sheet.add_image(self.get_watermark())
        # 页边距
        sheet.page_margins = PageMargins(top=1, left=0.3, bottom=1, right=0.3, header=1, footer=1)
        # 列宽, B-G:实际总宽27+27+7+8+7 - (0.62*5=72.9   B:F 81.28
        sheet.column_dimensions['A'].width = 9
        sheet.column_dimensions['B'].width = 27
        sheet.column_dimensions['E'].width = 27
        sheet.column_dimensions['C'].width = 7
        sheet.column_dimensions['D'].width = 8
        sheet.column_dimensions['F'].width = 7
        sheet.column_dimensions['G'].width = 9

        # logo
        logo_path = pathlib.Path(FILE_STORAGE).joinpath('AppImages/dailyReport/company_logo.png')
        company_logo = ODImage(logo_path)
        company_logo.width, company_logo.height = (120, 32)
        sheet.add_image(company_logo, 'A1')

        # 标题
        sheet['B1'] = self.report_basic['report_title']
        sheet['B1'].font = self.cell_font(size=self.TITLE_FONT_SIZE, bold=True, name=self.DEFAULT_FONT_NAME)
        sheet['B1'].alignment = self.cell_alignment(horizontal='center', vertical='center')
        sheet.merge_cells('B1:E1')
        # 日期
        sheet['F1'] = self.report_basic['report_date']
        sheet['F1'].font = self.cell_font(size=self.DATE_FONT_SIZE, bold=True, name=self.DEFAULT_FONT_NAME)
        sheet['F1'].alignment = self.cell_alignment(horizontal='right', vertical='center')
        sheet.merge_cells('F1:G1')

        # 作者
        sheet['A2'] = f'分析师:{self.report_basic["author"]["name"]}    从业资格证号:{self.report_basic["author"]["certificate1"]}   投资咨询从业证书号:{self.report_basic["author"]["certificate2"]}'
        sheet['A2'].font = self.cell_font(size=self.AUTHOR_FONT_SIZE, bold=True, name=self.AUTHOR_FONT_NAME)
        sheet['A2'].alignment = self.cell_alignment(horizontal='left', vertical='center')
        sheet.merge_cells('A2:G2')

        # 表格标题
        sheet['A3'] = '项目类别'
        sheet['A3'].font = self.cell_font(size=self.TABLE_HEADER_FONT_SIZE, color='ffffff', bold=True,
                                          name=self.TABLE_HEADER_FONT_NAME)
        # sheet['A3'].fill = cell_forward_color(color='0f6fc6')
        sheet['A3'].border = self.cell_border(top='medium')
        texts = ['数据指标', '最新', '环比']
        for i, col in enumerate(['B', 'C', 'D', 'E', 'F', 'G']):
            sheet[f'{col}3'] = texts[i % 3]
            sheet[f'{col}3'].font = self.cell_font(size=self.TABLE_HEADER_FONT_SIZE, color='ffffff', bold=True,
                                                   name=self.TABLE_HEADER_FONT_NAME)
            # sheet[f'{col}3'].fill = cell_forward_color(color='0f6fc6')
            sheet[f'{col}3'].border = self.cell_border(top='medium')
            if i % 3 == 0:
                sheet[f'{col}3'].alignment = self.cell_alignment(horizontal='center', vertical='center')
            else:
                sheet[f'{col}3'].alignment = self.cell_alignment(horizontal='right', vertical='center')

        # 写入指标数据,返回已使用的行号
        current_row = self.write_indexes_values(sheet, start_row=4)
        # 写入最后的备注
        sheet[f'A{current_row}'] = self.report_basic['note']
        sheet[f'A{current_row}'].border = self.cell_border(bottom='medium', top='medium')
        sheet[f'A{current_row}'].font = self.cell_font(size=self.NOTE_FONT_SIZE, bold=False, name=self.NOTE_FONT_NAME)
        sheet[f'A{current_row}'].alignment = self.cell_alignment(horizontal='left', vertical='center')
        sheet.merge_cells(f'A{current_row}:G{current_row}')

        # 前景色填充
        for letter in ['A', 'B', 'C', 'D', 'E', 'F', 'G']:  # A-G
            for col in range(1, current_row):
                sheet[f'{letter}{col}'].fill = self.cell_forward_color(color='ffffff')
                if ord(letter) <= ord('G') and col == 3:
                    sheet[f'{letter}{col}'].fill = self.cell_forward_color(color='0f6fc6')
        temp_folder = pathlib.Path('Temp/dailyReport/')
        save_folder = pathlib.Path(FILE_STORAGE).joinpath(temp_folder)
        filename = f'{self.report_basic["report_title"]}{self.report_basic["report_date"].replace("-", "")}.xlsx'
        save_file = save_folder.joinpath(filename)
        work_book.save(save_file)
        return str(pathlib.Path(temp_folder).joinpath(filename))


class ReportPdfFileHandler(object):
    LEFT_MARGIN, TOP_MARGIN, RIGHT_MARGIN, BOTTOM_MARGIN = (cm / 2, cm, cm / 2, 2 * cm)

    def __init__(self, report_basic, indexes_categories, msg_categories, block: int):
        self.report_basic = report_basic
        self.indexes_categories = indexes_categories
        self.msg_categories = msg_categories
        self.block_flag = block

    @staticmethod
    def strQ2B(ustring):
        """ 全角转半角 """
        rstring = ""
        for uchar in ustring:
            inside_code = ord(uchar)
            if inside_code == 12288:  # 全角空格直接转换
                inside_code = 32
            elif 65281 <= inside_code <= 65374:  # 全角字符（除空格）根据关系转化
                inside_code -= 65248
            else:
                pass
            rstring += chr(inside_code)
        return rstring

    def first_page_event(self, canvas, doc):
        canvas.saveState()
        # canvas.setFont('SyH_N', 9)
        # canvas.drawString(self.LEFT_MARGIN, 0.75 * inch, "%s Page %d" % ('产业日报', doc.page))

        canvas.translate(0.25 * PAGE_HEIGHT, 0)
        canvas.rotate(30)
        # canvas.setStrokeColorRGB(1, 0, 0)  # 文字轮廓色彩(似乎不起作用)
        canvas.setFillColorRGB(1, 0.6, 0.6, 0.28)  # 文字填充色(默认就是黑色)
        # canvas.setFillAlpha(0.3)
        canvas.setFont('SyH_N', 65)
        canvas.drawString(self.LEFT_MARGIN * 6.5, (PAGE_HEIGHT - self.TOP_MARGIN - self.BOTTOM_MARGIN) / 2, '瑞    达    研    究')
        # canvas.drawRightString(PAGE_WIDTH - inch * 2.5, inch, '瑞达研究')
        canvas.restoreState()
        # print('第一页')  # 第一页运行一次

    def later_page_event(self, canvas, doc):
        canvas.saveState()
        # canvas.setFont('SyH_N', 9)
        # canvas.drawString(self.LEFT_MARGIN, 0.75 * inch, "%s Page %d" % ('产业日报', doc.page))

        # 水印开始
        canvas.translate(0.25 * PAGE_HEIGHT, 0)
        canvas.rotate(30)
        canvas.setFillColorRGB(1, 0.6, 0.6, 0.28)  # 文字填充色(默认就是黑色)
        canvas.setFont('SyH_N', 65)
        canvas.drawString(self.LEFT_MARGIN * 6.5, (PAGE_HEIGHT - self.TOP_MARGIN - self.BOTTOM_MARGIN) / 2, '瑞    达    研    究')
        # 水印结束
        canvas.restoreState()
        # print('每页出来', doc.page)  # 第二页开始每页运行一次

    def create_pdf_file(self):
        if self.report_basic["report_title"] == '不锈钢产业日报':
            with open('temp_ss.json', 'w', encoding='utf8') as f:
                json.dump(self.indexes_categories, f, indent=2, ensure_ascii=False)
        save_folder = pathlib.Path(FILE_STORAGE).joinpath('Temp/dailyReport/')
        filename = f'{self.report_basic["report_title"]}{self.report_basic["report_date"].replace("-", "")}.pdf'
        save_file = save_folder.joinpath(filename)
        save_file = str(save_file).replace('\\', '/')
        doc = SimpleDocTemplate(save_file, leftMargin=self.LEFT_MARGIN, topMargin=self.TOP_MARGIN,
                                rightMargin=self.RIGHT_MARGIN, bottomMargin=self.BOTTOM_MARGIN)  # 默认页面距=inch
        story = list()  # Spacer(1, 4*cm)
        story.append(Spacer(1, cm))
        # logo
        data = [[Image(pathlib.Path(FILE_STORAGE).joinpath('AppImages/dailyReport/company_logo.png'), width=3 * cm, height=0.8 * cm,
                       useDPI=True),
                 self.report_basic['report_title'], self.report_basic['report_date']]]
        t = Table(data, style=TableStyle([
            # ('GRID', (0, 0), (-1, -1), 1, colors.green),
            ('FONTNAME', (1, 0), (1, 0), 'SyH_B'),
            ('FONTSIZE', (1, 0), (1, 0), 24),
            ('FONTSIZE', (2, 0), (2, 0), 16),
            ('TOPPADDING', (1, 0), (1, 0), -8),
            ('LEFTPADDING', (0, 0), (0, -1), 0),
            ('RIGHTPADDING', (2, 0), (2, -1), 0),
            ('ALIGN', (0, 0), (0, 0), 'LEFT'),
            ('ALIGN', (1, 0), (1, 0), 'CENTER'),
            ('ALIGN', (2, 0), (2, 0), 'RIGHT'),
            ('VALIGN', (0, 0), (0, 0), 'BOTTOM'),
            ('VALIGN', (1, 0), (2, 0), 'TOP')
        ]), colWidths=([(PAGE_WIDTH - 2 * self.LEFT_MARGIN) / 3 for _ in range(3)]))
        story.append(t)
        author = self.report_basic['author']
        # story.append(
        #     Paragraph(f'分析师:{author["name"]}    从业资格证号:{author["certificate1"]}    投资咨询从业证号:{author["certificate2"]}',
        #               ParagraphStyle(fontName='SyH_N', name='Song', fontSize=9, leftIndent=-6)))
        # 数据表格
        t_data = [['项目类别', '数据指标', '最新', '环比', '数据指标', '最新', '环比']]
        t_style = [
            ('LINEABOVE', (0, 0), (-1, 0), 1.2, colors.black),
            ('FONTNAME', (0, 0), (-1, 0), 'SyH_B'),
            ('FONTNAME', (0, 1), (0, -1), 'SyH_N'),
            ('FONTSIZE', (0, 0), (-1, 0), 11),
            ('FONTSIZE', (0, 1), (-1, -1), 9),
            ('TOPPADDING', (0, 1), (-1, -1), 0.4),
            ('BOTTOMPADDING', (0, 1), (-1, -1), 0.4),
            ('RIGHTPADDING', (3, 0), (3, -1), 1.8),
            ('RIGHTPADDING', (6, 0), (6, -1), 0.5),
            ('LEFTPADDING', (4, 1), (4, -1), 1.8),
            ('LEFTPADDING', (1, 1), (1, -1), 1.8),
            ('BACKGROUND', (0, 0), (-1, 0), '#0e6ec5'),
            ('TEXTCOLOR', (0, 0), (-1, 0), colors.white),
            ('ALIGN', (0, 0), (1, 0), 'CENTER'),
            ('ALIGN', (0, 0), (0, -1), 'CENTER'),
            ('ALIGN', (4, 0), (4, 0), 'CENTER'),
            ('ALIGN', (2, 0), (3, -1), 'RIGHT'),
            ('ALIGN', (5, 0), (6, -1), 'RIGHT'),
            ('VALIGN', (0, 0), (-1, -1), 'MIDDLE'),
        ]

        current_row = 1
        for category in self.indexes_categories:
            temp_list = []
            category_indexes = list(filter(lambda x: x['is_active'], category['indexes']))
            block_start_row = current_row
            # 指标数据
            group_total_count = len(category_indexes)
            for i, item in enumerate(category_indexes):
                if not item['is_active']:
                    continue
                # print(category['category_name'], i, item)
                if i == 0:
                    temp_list.append(category['category_name'])
                temp_list.append(item['index_name'])
                temp_list.append(item['value'])
                temp_list.append(item['diff'])
                if i % 2 == 1:
                    # 设置文字颜色
                    for col in [3, 6]:
                        if float(temp_list[col]) > 0:
                            t_style.append(('TEXTCOLOR', (col, current_row), (col, current_row), colors.red))
                        elif float(temp_list[col]) < 0:
                            t_style.append(('TEXTCOLOR', (col, current_row), (col, current_row), colors.blue))
                        else:
                            pass
                    for col in [1, 4]:
                        temp_list[col] = Paragraph(self.strQ2B(str(temp_list[col])),
                                                   ParagraphStyle(fontName='SyH_N', name='Song', fontSize=9))
                    # 换行
                    t_data.append(temp_list.copy())
                    temp_list = ['']
                    current_row = current_row + 1
                if group_total_count % 2 == 1 and i == group_total_count - 1:  # 当总数是奇数的时候，且最后一个时。
                    # print(temp_list)
                    # 设置第一列（指标名称）字体
                    temp_list[1] = Paragraph(self.strQ2B(str(temp_list[1])), ParagraphStyle(fontName='SyH_N', name='Song', fontSize=9))
                    # 设置第3列(即涨跌)文字颜色
                    if float(temp_list[3]) > 0:
                        t_style.append(('TEXTCOLOR', (3, current_row), (3, current_row), colors.red))
                    elif float(temp_list[3]) < 0:
                        t_style.append(('TEXTCOLOR', (3, current_row), (3, current_row), colors.blue))
                    else:
                        pass
                    t_data.append(temp_list.copy())
                    temp_list = ['']
                    current_row = current_row + 1
            block_end_row = current_row - 1
            # 添加上边框黑线
            t_style.append(('LINEABOVE', (0, current_row), (-1, current_row), 1.2, colors.black))
            t_style.append(('SPAN', (0, block_start_row), (0, block_end_row)))  # 合并
            # t_style.append(('VALIGN', (0, block_start_row), (0, block_end_row), 'MIDDLE'))  # 居中
            # t_style.append(('ALIGN', (0, block_start_row), (0, block_end_row), 'CENTER'))  # 居中
        t_style.append(('LINEAFTER', (3, 1), (3, current_row - 1), 0.5, colors.grey))
        # 信息数据
        for j, msg in enumerate(self.msg_categories):
            msg_cells = [Paragraph(msg['category_name'], ParagraphStyle(fontName='SyH_N', name='Song', leading=14, fontSize=9)),
                         Paragraph(msg['message'],
                                   ParagraphStyle(fontName='SyH_N', name='Song', leading=14, fontSize=9)),
                         '', '', '', '', '']
            # 将推广二维码图贴在前2类文字右侧，看起来不好看
            if j == 0:
                msg_cells[5] = Image(pathlib.Path(FILE_STORAGE).joinpath('AppImages/dailyReport/yjyqrcode.png'), width=70, height=82, useDPI=True, hAlign='RIGHT')
                t_style.append(('SPAN', (1, current_row), (4, current_row)))
                t_style.append(('SPAN', (5, current_row), (6, current_row)))
                t_style.append(('VALIGN', (5, current_row), (6, current_row), 'TOP'))
                t_style.append(('RIGHTPADDING', (5, current_row), (6, current_row), 0))
            elif j == 1:
                msg_cells[5] = Image(pathlib.Path(FILE_STORAGE).joinpath('AppImages/dailyReport/rzqrcode.png'), width=70, height=82, useDPI=True, hAlign='RIGHT')
                t_style.append(('SPAN', (1, current_row), (4, current_row)))
                t_style.append(('SPAN', (5, current_row), (6, current_row)))
                t_style.append(('VALIGN', (5, current_row), (6, current_row), 'TOP'))
                t_style.append(('RIGHTPADDING', (5, current_row), (6, current_row), 0))
            else:
                t_style.append(('SPAN', (1, current_row), (6, current_row)))
            t_data.append(msg_cells)
            t_style.append(('LINEABOVE', (0, current_row), (-1, current_row), 1.2, colors.black))
            current_row = current_row + 1
        t_style.append(('LEFTPADDING', (0, 1), (0, -1), 0))
        t_style.append(('RIGHTPADDING', (0, 1), (0, -1), 0))
        t_style.append(('LINEAFTER', (0, 1), (0, current_row + 1), 0.7, colors.black))
        t_style.append(('LINEBELOW', (0, current_row - 1), (-1, current_row - 1), 1.2, colors.black))  # 最后一行的边框
        # print(PAGE_WIDTH - 2 * self.LEFT_MARGIN)
        story.append(Table(t_data, style=TableStyle(t_style), colWidths=(46.58, 169, 51, 41, 169, 51, 41)))
        story.append(Spacer(1, 0.1 * cm))
        # 说明
        story.append(Paragraph(self.report_basic['note'], ParagraphStyle(fontName='SyH_N', name='Song', fontSize=8, leftIndent=-6)))
        story.append(Spacer(1, 0.2 * cm))
        # # 推广图片
        # qr_code_data = [
        #     [Image(pathlib.Path(FILE_STORAGE).joinpath('AppImages/dailyReport/linkqrcode.png'), width=460, height=130, useDPI=True)],
        # ]
        # story.append(Table(qr_code_data, style=TableStyle([('ALIGN', (0, 0), (-1, -1), 'CENTER')]), colWidths=[568]))
        # story.append(Spacer(1, 1.5 * cm))
        # print(PAGE_WIDTH - self.LEFT_MARGIN - self.RIGHT_MARGIN)
        # 研究员信息
        research_data = [
            [Paragraph(t, ParagraphStyle(fontName='SyH_N', name='Song', fontSize=9)) for t in ['', '研究员:', '蔡跃辉', '期货从业资格号F0251444', '期货投资咨询从业证书号Z0013101']]
        ]
        research_data[0][1] = Paragraph('研究员:', ParagraphStyle(fontName='SyH_B', name='Song', fontSize=9, alignment=0))
        research_style = [
            # ('GRID', (0, 0), (-1, -1), 1, colors.green),
            ('ALIGN', (1, 0), (1, -1), 'RIGHT'),
        ]
        story.append(Table(research_data, style=TableStyle(research_style), colWidths=(170, 65, 50, 120, 165)))
        # 助理研究员
        zhuli_data1 = [
            [Paragraph(t, ParagraphStyle(fontName='SyH_N', name='Song', fontSize=9)) for t in ['', '助理研究员:', '李秋荣', '期货从业资格号F03101823', '王凯慧', '期货从业资格号F03100511']],
            [Paragraph(t, ParagraphStyle(fontName='SyH_N', name='Song', fontSize=9)) for t in ['', '', '王世霖', '期货从业资格号F03118150', '', '']]
        ]
        zhuli_data1[0][1] = Paragraph('助理研究员:', ParagraphStyle(fontName='SyH_B', name='Song', fontSize=9, alignment=0))

        zhuli_data2 = [
            [Paragraph(t, ParagraphStyle(fontName='SyH_N', name='Song', fontSize=9)) for t in ['', '助理研究员:', '尤正宇', '期货从业资格号F03111199', '郑嘉岚', ' 期货从业资格号F03110073']],
        ]
        zhuli_data2[0][1] = Paragraph('助理研究员:', ParagraphStyle(fontName='SyH_B', name='Song', fontSize=9, alignment=0))

        zhuli_data3 = [
            [Paragraph(t, ParagraphStyle(fontName='SyH_N', name='Song', fontSize=9)) for t in ['', '助理研究员:', '谢程珙', ' 期货从业资格号F03117498', '', '']],
        ]
        zhuli_data3[0][1] = Paragraph('助理研究员:', ParagraphStyle(fontName='SyH_B', name='Song', fontSize=9, alignment=0))

        zhuli_data4 = [
            [Paragraph(t, ParagraphStyle(fontName='SyH_N', name='Song', fontSize=9)) for t in ['', '助理研究员:', '廖宏斌', '期货从业资格号F3082507', '王世霖', '期货从业资格号F03118150']],
            [Paragraph(t, ParagraphStyle(fontName='SyH_N', name='Song', fontSize=9)) for t in ['', '', '曾奕蓉', ' 期货从业资格号F03105260', '', '']]
        ]
        zhuli_data4[0][1] = Paragraph('助理研究员:', ParagraphStyle(fontName='SyH_B', name='Song', fontSize=9, alignment=0))

        if self.block_flag == 1:  # 有色，黑色
            zhuli_data = zhuli_data1
        elif self.block_flag == 2:  # 化工
            zhuli_data = zhuli_data2
        elif self.block_flag == 3:  # 农产品
            zhuli_data = zhuli_data3
        elif self.block_flag == 4:  # 宏观
            zhuli_data = zhuli_data4
        else:
            zhuli_data = []

        story.append(Table(zhuli_data, style=TableStyle(research_style), colWidths=(170, 65, 50, 120, 45, 120)))

        story.append(Spacer(1, 0.6 * cm))

        # 免责声明
        story.append(Paragraph('免责声明', ParagraphStyle(fontName='SyH_B', name='SyH_B', fontSize=14)))
        text = "本报告中的信息均来源于公开可获得资料，瑞达期货股份有限公司力求准确可靠，但对这些信息的准确性及完整性不做任何保证，据此投资，责任自负。本报告不构成个人投资建议，客户应考虑本报告中的任何意见或建议是否符合其特定状况。本报告版权仅为我公司所有，未经书面许可，任何机构和个人不得以任何形式翻版、复制和发布。如引用、刊发，需注明出处为瑞达期货股份有限公司研究院，且不得对本报告进行有悖原意的引用、删节和修改。"
        story.append(Paragraph(text, ParagraphStyle(fontName='SyH_N', name='SyH_N', fontSize=10, leading=16, spaceBefore=14, firstLineIndent=22)))

        # # 二维码推广图片放在最下方
        # qr_code_data = [
        #     [Image(pathlib.Path(FILE_STORAGE).joinpath('AppImages/dailyReport/yjyqrcode.png'), width=80, height=90, useDPI=True),
        #      Image(pathlib.Path(FILE_STORAGE).joinpath('AppImages/dailyReport/rzqrcode.png'), width=80, height=90, useDPI=True)]
        # ]
        # story.append(
        #     Table(qr_code_data, style=TableStyle([('ALIGN', (0, 0), (-1, -1), 'CENTER')]), colWidths=(150, 150)))

        doc.build(story, onFirstPage=self.first_page_event, onLaterPages=self.later_page_event)

        return str(pathlib.Path('Temp/dailyReport/').joinpath(filename))


@daily_report_api.post('/exportExcel/')  # 导出日报excel
def export_daily_report(ruizy_token: str = Header(...), content_item: ExportContentItem = Body(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    if content_item.variety_en not in VARIETY.VARIETY_NAMES.keys():
        return AllResponse.validate_error(msg='品种代码错误,导出失败！')
    # 前端传上日报数据，生成excel
    report_basic = {
        'report_title': content_item.report_title,
        'report_date': content_item.report_date,
        'author': jsonable_encoder(content_item.author),
        'note': content_item.note
    }
    index_categories = jsonable_encoder(content_item.index_categories)
    msg_categories = jsonable_encoder(content_item.msg_categories)
    excel_handler = ReportExcelFileHandler(report_basic=report_basic,
                                           indexes_categories=index_categories,
                                           msg_categories=msg_categories)
    temp_filename = excel_handler.create_excel_file()
    filename = temp_filename.replace('\\', '/')
    response_data = {'report_file': f'{WEB_HOST}{filename}'}

    # 记录日报固定数据和导出记录 20220824
    filename = content_item.report_title + str(datetime_utils.today_timestamp(millisecond=True)) + '.json'
    # print(filename)
    date_folder = content_item.report_date.replace('-', '')
    save_path, sql_path = file_utils.get_file_paths(module_name='DailyReport', filename=filename,
                                                    hashed=True, date_folder=date_folder)
    # print(save_path, sql_path)
    with open(save_path, 'w', encoding='utf8') as fp:
        json.dump(jsonable_encoder(content_item), fp, indent=2, ensure_ascii=False)
    # 查询数据库是否存在记录
    db_conn = FAConnection()
    exist_sql = "SELECT id,file_url FROM report_export_fixed WHERE author=%s AND report_date=%s AND variety_en=%s;"
    exist = db_conn.query(exist_sql, param=[person['uid'], date_folder, content_item.variety_en], fetchone=True,
                          keep_conn=True)[0]  # 上文用的date_folder就是报告日期
    now_time = datetime_utils.today(f='%Y-%m-%d %H:%M:%S')
    if exist:  # 修改
        sql = "UPDATE report_export_fixed SET title=%s,fixed_time=%s,file_url=%s WHERE id=%s LIMIT 1;"
        prm = [date_folder + content_item.report_title, now_time, sql_path, exist['id']]
        _, success = db_conn.execute(sql, param=prm)
        if success:  # 删除旧文件
            old_json_file = pathlib.Path(FILE_STORAGE).joinpath(exist['file_url'])
            if old_json_file.exists() and old_json_file.is_file():
                old_json_file.unlink()
    else:  # 增加
        sql = """
            INSERT INTO report_export_fixed (report_date,variety_en,fixed_time,title,author,file_url) 
            VALUES (%s,%s,%s,%s,%s,%s);
        """
        prm = [date_folder, content_item.variety_en, now_time, date_folder + content_item.report_title, person['uid'],
               sql_path]
        success, _ = db_conn.insert(sql, param=prm)
    return AllResponse.operate_successfully(data=response_data) if success else AllResponse.server_error()


@daily_report_api.post('/exportPdf/')  # 导出日报pdf
def export_daily_pdf_report(ruizy_token: str = Header(...), content_item: ExportContentItem = Body(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    # if person['uid'] != 2:
    #     return AllResponse.validate_error(msg='自动生成的PDF报告格式还未经部门允许，本功能暂未开放.')
    if content_item.variety_en not in VARIETY.VARIETY_NAMES.keys():
        return AllResponse.validate_error(msg='品种代码错误,导出失败！')
    # 前端传上日报数据，生成pdf
    report_basic = {
        'report_title': content_item.report_title,
        'report_date': content_item.report_date,
        'author': jsonable_encoder(content_item.author),
        'note': content_item.note
    }
    print(content_item.variety_en)

    block_dict = {
        'AP': 3,
        'BR': 2,
        'BU': 2,
        'CJ': 3,
        'CU': 1,
        'EB': 2,
        'EC': 4,
        'EG': 2,
        'FU': 2,
        'HC': 1,
        'I': 1,
        'JD': 3,
        'L': 2,
        'LC': 1,
        'LH': 3,
        'MA': 2,
        'NI': 1,
        'P': 3,
        'PB': 1,
        'PF': 2,
        'PG': 2,
        'PK': 3,
        'PP': 2,
        'RABMY': 3,
        'RALAO': 1,
        'RAUAG': 1,
        'RB': 1,
        'RCFCY': 3,
        'RDCCS': 3,
        'REGTA': 2,
        'RFGSA': 2,
        'RHONG': 4,
        'RIFHC': 4,
        'ROIMS': 3,
        'RSMSF': 1,
        'RSTEEL': 1,
        'RU': 2,
        'RZJMJ': 1,
        'RZTGZ': 4,
        'SC': 2,
        'SI': 1,
        'SN': 1,
        'SP': 2,
        'SR': 3,
        'SS': 1,
        'TA': 2,
        'UR': 2,
        'V': 2,
        'ZC': 1,
        'ZN': 1,
        'SH': 2
    }

    block_flag = block_dict.get(content_item.variety_en, 0)
    index_categories = jsonable_encoder(content_item.index_categories)
    msg_categories = jsonable_encoder(content_item.msg_categories)
    pdf_handler = ReportPdfFileHandler(report_basic=report_basic,
                                       indexes_categories=index_categories,
                                       msg_categories=msg_categories,
                                       block=block_flag)
    temp_filename = pdf_handler.create_pdf_file().replace('\\', '/')

    file_date = content_item.report_date.replace('-', '')
    folder = pathlib.Path(FILE_STORAGE).joinpath(f'Annexes/PeriodicReport/Daily/{file_date}')
    if not folder.exists():
        folder.mkdir(parents=True)
    uuid_str = chr(random.randint(97, 122)) + ''.join(str(uuid.uuid4()).split('-'))[1:]
    targe_filename = str(int(time.time() * 1000)) + uuid_str + pathlib.Path(temp_filename).suffix
    target_filepath = folder.joinpath(targe_filename)
    title = pathlib.Path(temp_filename).name.replace('.pdf', '')
    sql_filepath = str(target_filepath).replace('\\', '/').replace(FILE_STORAGE, '')
    # 维护数据库记录
    # print(file_date, content_item.variety_en)
    # print('源文件', temp_filename)
    # print('目的地', target_filepath)
    # print('数据库', sql_filepath)
    shutil.copy(pathlib.Path(FILE_STORAGE).joinpath(temp_filename), target_filepath)  # 将文件移动到附件文件夹
    db_conn = FAConnection()
    exist_sql = """
        SELECT id,filepath FROM report_periodic_file 
        WHERE creator_id=%s AND file_date=%s AND variety_en=%s AND file_type='DAILY'
        LIMIT 1;
    """
    exist = db_conn.query(exist_sql, param=[person['uid'], file_date, content_item.variety_en], keep_conn=True, fetchone=True)[0]
    if exist:  # 修改记录
        u_sql = """
            UPDATE report_periodic_file SET title=%s,filepath=%s,
            examine_time=NULL,examine_name='#',examine_state=1,reading=0
            WHERE id=%s LIMIT 1;
        """
        _, success = db_conn.execute(u_sql, param=[title, sql_filepath, exist['id']])
        if success:  # 删除原来的文件
            old_file = pathlib.Path(FILE_STORAGE).joinpath(exist.get('filepath', ''))
            if old_file.exists() and old_file.is_file():
                old_file.unlink()
    else:  # 添加记录
        sql = """
            INSERT INTO report_periodic_file
            (file_date,creator_id,variety_en,title,file_type,filepath,examine_state,examine_name)
            VALUES (%s,%s,%s,%s,%s,%s,%s,%s);
        """
        prm = [file_date, person['uid'], content_item.variety_en, title, 'DAILY', sql_filepath, 1, '#']
        success, _ = db_conn.insert(sql, param=prm)  # 这里的success是添加成功的数量
    if not success:  # 如果数据库没有添加成功，移除文件
        if target_filepath.exists() and target_filepath.is_file():
            target_filepath.unlink()
    response_data = {'report_file': f'{WEB_HOST}{temp_filename}'}
    # print(success)
    return AllResponse.operate_successfully(data=response_data) if success else AllResponse.server_error()


class BasicItem(BaseModel):
    variety_en: constr(min_length=1)
    author: constr(min_length=1)
    certificate1: constr(min_length=1)
    certificate2: constr(min_length=1)
    note: constr(min_length=1)


@daily_report_api.post('/basic/')  # 保存日报的基本信息
async def daily_report_basic(ruizy_token: str = Header(...), basic_item: BasicItem = Body(...)):
    user_obj = security.decrypt_access_token(ruizy_token)
    if not user_obj:
        return AllResponse.no_authorization()
    today = int(datetime.datetime.today().strftime('%Y%m%d'))
    db_conn = FAConnection()
    exist_sql = "SELECT id FROM report_daily_basic WHERE create_date=%s AND variety_en=%s;"
    exist_obj = db_conn.query(exist_sql, param=(today, basic_item.variety_en), fetchone=True, keep_conn=True)[0]

    if exist_obj:
        sql = "UPDATE report_daily_basic SET author=%s,certificate1=%s,certificate2=%s,note=%s WHERE id=%s LIMIT 1;"
        prm = (basic_item.author, basic_item.certificate1, basic_item.certificate2, basic_item.note, exist_obj['id'])
    else:
        sql = """
            INSERT INTO report_daily_basic(create_date,variety_en,author,certificate1,certificate2,note) 
            VALUES (%s,%s,%s,%s,%s,%s);
        """
        prm = (today, basic_item.variety_en, basic_item.author, basic_item.certificate1,
               basic_item.certificate2, basic_item.note)

    _, success = db_conn.execute(sql, param=prm)

    return AllResponse.operate_successfully() if success else AllResponse.server_error()


@daily_report_api.get('/basic/')  # 查询日报的基本信息
async def retrieve_report_basic(v: str = Query(...)):
    sql = """
        SELECT id,variety_en,author,certificate1,certificate2,note FROM report_daily_basic 
        WHERE variety_en=%s ORDER BY create_date DESC LIMIT 1;
    """
    db_conn = FAConnection()
    basic_obj = db_conn.query(sql, param=[v], fetchone=True)[0]
    return AllResponse.operate_successfully(data=basic_obj)


class ReportCategory(BaseModel):
    variety_en: constr(min_length=1)
    category_name: constr(min_length=1)
    category_group: constr(min_length=1)
    sorted: int


@daily_report_api.post('/category/')  # 新增或更新一个日报项目
def daily_report_category(ruizy_token: str = Header(...), category_item: ReportCategory = Body(...)):
    user_obj = security.decrypt_access_token(ruizy_token)
    if not user_obj:
        return AllResponse.no_authorization()
    # 查询类别是否存在，存在更新，不存在则新增
    db_conn = FAConnection()
    exist_sql = """
        SELECT id FROM report_daily_category WHERE category_name=%s AND variety_en=%s AND category_group=%s;
    """
    exist_prm = (category_item.category_name, category_item.variety_en, category_item.category_group)
    exist_obj = db_conn.query(exist_sql, param=exist_prm, fetchone=True, keep_conn=True)[0]
    if exist_obj:
        sql = "UPDATE report_daily_category SET category_name=%s,sorted=%s WHERE id=%s LIMIT 1;"
        prm = (category_item.category_name, category_item.sorted, exist_obj['id'])
        _, success = db_conn.execute(sql, prm)
        response = {
            'id': exist_obj['id'],
            'variety_en': category_item.variety_en,
            'category_name': category_item.category_name,
            'sorted': category_item.sorted
        }
    else:
        sql = "INSERT INTO report_daily_category (variety_en,category_name,category_group,sorted) VALUES (%s,%s,%s,%s);"
        prm = (category_item.variety_en, category_item.category_name, category_item.category_group, category_item.sorted)
        success, new_id = db_conn.insert(sql, prm)
        response = {
            'id': new_id,
            'variety_en': category_item.variety_en,
            'category_group': category_item.category_group,
            'category_name': category_item.category_name,
            'sorted': category_item.sorted
        }
    return AllResponse.operate_successfully(data=response) if success else AllResponse.server_error()


@daily_report_api.get('/category/')  # 查询品种日报项目及其内的指标
def variety_daily_report(v: str = Query(...), date: str = Query(..., min_length=8, max_length=8)):
    query_date = datetime_utils.auth_datetime_string(datetime_string=date, f='%Y%m%d')
    if not query_date:
        return AllResponse.validate_error(msg='查询参数错误!')
    variety_en = v
    sql = """
        SELECT a.id,a.variety_en,a.category_name,a.category_group,a.sorted As c_sorted, 
               b.id As index_id,b.category_id,b.index_name,b.formula,b.frequency,b.sorted As i_sorted,b.is_active,
               v.variety_name  
        FROM report_daily_category AS a 
        INNER JOIN ruizy_variety As v ON a.variety_en=v.variety_en 
        LEFT JOIN report_daily_index AS b ON a.id=b.category_id 
        WHERE a.variety_en=%s ORDER BY a.sorted;
    """
    db_conn = FAConnection()
    records = db_conn.query(sql, param=[variety_en], keep_conn=True)
    # 查询今日的品种的消息
    msg_sql = """
        SELECT id,report_date,variety_en,category_id,message FROM report_daily_message 
        WHERE report_date=%s AND variety_en=%s;
    """
    message_data = db_conn.query(msg_sql, param=(query_date, variety_en), keep_conn=True)
    msg_category_data = {item['category_id']: item['message'] for item in message_data}
    # 查询最大日期的日报基本信息
    basic_sql = """
        SELECT id,create_date,variety_en,author,certificate1,certificate2,note FROM report_daily_basic 
        WHERE variety_en=%s ORDER BY create_date DESC LIMIT 1;
    """
    basic_data = db_conn.query(basic_sql, param=[variety_en], fetchone=True)[0]
    report_author = 'unknown'
    certificate1 = 'unknown'
    certificate2 = 'unknown'
    report_note = ''
    if basic_data:
        report_author = basic_data['author']
        certificate1 = basic_data['certificate1']
        certificate2 = basic_data['certificate2']
        report_note = basic_data['note']

    # 处理数据
    category_indexes = {}  # 指标组的类别
    category_messages = []  # 消息组的类别
    for c_item in records:
        if c_item['category_group'] == 'index':
            c_obj = category_indexes.get(c_item['id'])
            if not c_obj:
                c_obj = {
                    'id': c_item['id'],
                    'category_name': c_item['category_name'],
                    'category_group': c_item['category_group'],
                    'variety_en': c_item['variety_en'],
                    'variety_name': c_item['variety_name'],
                    'sorted': c_item['c_sorted'],
                    'indexes': []
                }
                category_indexes[c_item['id']] = c_obj
            if c_item['category_id']:
                c_obj['indexes'].append({
                    'id': c_item['index_id'],
                    'category_id': c_item['id'],
                    'index_name': c_item['index_name'],
                    'formula': c_item['formula'],
                    'frequency': c_item['frequency'],
                    'sorted': c_item['i_sorted'],
                    'pre_date': 'NAN',
                    'pre_value': 'NAN',
                    'value': 'NAN',
                    'diff': 'NAN',
                    'is_loaded': False if c_item['is_active'] else True,
                    'is_active': c_item['is_active']
                })
        else:
            category_msg = msg_category_data.get(c_item['id'])
            category_messages.append({
                'id': c_item['id'],
                'category_name': c_item['category_name'],
                'category_group': c_item['category_group'],
                'variety_en': c_item['variety_en'],
                'variety_name': c_item['variety_name'],
                'sorted': c_item['c_sorted'],
                'message': '今日暂无消息' if not category_msg else category_msg
            })
    index_category_list = sorted([v for k, v in category_indexes.items()], key=lambda x: (x['sorted'], x['id']))  # 指标组排序
    for c in index_category_list:
        c['indexes'] = sorted(c['indexes'], key=lambda x: (x['sorted'], x['id']))  # 指标排序

    msg_category_list = sorted(category_messages, key=lambda x: x['sorted'])
    response_data = {
        'index_categories': index_category_list,
        'msg_categories': msg_category_list,
        'author': report_author,
        'certificate1': certificate1,
        'certificate2': certificate2,
        'note': report_note
    }
    return AllResponse.operate_successfully(data=response_data)


@daily_report_api.delete('/category/')  # 删除项目指标
def delete_report_category(ruizy_token: str = Header(...), cid: int = Body(..., ge=1, embed=True)):
    user_obj = security.decrypt_access_token(ruizy_token)
    if not user_obj:
        return AllResponse.no_authorization()
    # 删除分类及其下的指标
    del_category_sql = "DELETE FROM report_daily_category WHERE id=%s LIMIT 1;"
    del_category_idx = "DELETE FROM report_daily_index WHERE category_id=%s;"
    db_conn = FAConnection()
    success = db_conn.execute_tasks(sql_list=[del_category_sql, del_category_idx], param_list=[[cid], [cid]])
    return AllResponse.operate_successfully() if success else AllResponse.server_error()


class ReportIndex(BaseModel):
    id: int
    category_id: conint(ge=1)
    index_name: constr(min_length=1)
    formula: constr(min_length=1)
    frequency: str
    sorted: int
    is_active: conint(ge=0, le=1)

    @validator('frequency')
    def validate_frequency(cls, value):
        if value not in constant.DATA_INDEX_FREQUENCY:
            raise ValidationError('频率参数错误!')
        return value


@daily_report_api.post('/category/index/')  # 新增或更新一个类别里的指标
def category_index(ruizy_token: str = Header(...), index_item: ReportIndex = Body(...)):
    user_obj = security.decrypt_access_token(ruizy_token)
    if not user_obj:
        return AllResponse.no_authorization()
    if not datalib_utils.formula_is_correct(index_item.formula):
        return AllResponse.validate_error(msg='公式设置不准确,添加失败！')
    db_conn = FAConnection()
    if index_item.id>0:  # 修改
        sql = "UPDATE report_daily_index SET index_name=%s,formula=%s,frequency=%s,sorted=%s,is_active=%s WHERE id=%s;"
        prm = (index_item.index_name, index_item.formula, index_item.frequency, index_item.sorted,
               index_item.is_active, index_item.id)
        _, success = db_conn.execute(sql, prm)
        index_id = index_item.id
    else:
        # 添加入库
        sql = """
            INSERT INTO report_daily_index(category_id,index_name,formula,frequency,sorted,is_active) 
            VALUES (%s,%s,%s,%s,%s,%s);
        """
        prm = (index_item.category_id, index_item.index_name, index_item.formula, index_item.frequency,
               index_item.sorted, index_item.is_active)
        success, index_id = db_conn.insert(sql, prm)
    response = {
        'id': index_id,
        'index_name': index_item.index_name,
        'frequency': index_item.frequency,
        'pre_date': None,
        'pre_value': None,
        'pre_diff': None,
        'value': None,
        'diff': None
    }
    return AllResponse.operate_successfully(data=response) if success else AllResponse.server_error()


@daily_report_api.delete('/category/index/')  # 删除一个类别里的指标
def delete_category_index(ruizy_token: str = Header(...), iid: int = Body(..., ge=1, embed=True)):
    user_obj = security.decrypt_access_token(ruizy_token)
    if not user_obj:
        return AllResponse.no_authorization()
    del_category_idx = "DELETE FROM report_daily_index WHERE id=%s LIMIT 1;"
    db_conn = FAConnection()
    _, success = db_conn.execute(del_category_idx, param=[iid])
    return AllResponse.operate_successfully() if success else AllResponse.server_error()


class ReportMessage(BaseModel):
    report_date: datetime_parse.date
    variety_en: constr(min_length=1)
    category_id: conint(ge=1)
    message: str

    def integer_report_date(self):
        return int(self.report_date.strftime('%Y%m%d'))


@daily_report_api.post('/category/message/')  # 新增或更新一个日报的消息
def category_message(ruizy_token: str = Header(...), msg_item: ReportMessage = Body(...)):
    user_obj = security.decrypt_access_token(ruizy_token)
    if not user_obj:
        return AllResponse.no_authorization()
    # 查询该品种今日这类消息是否存在
    exist_sql = "SELECT id FROM report_daily_message WHERE report_date=%s AND variety_en=%s AND category_id=%s;"
    exist_prm = (msg_item.integer_report_date(), msg_item.variety_en, msg_item.category_id)
    db_conn = FAConnection()
    exist_obj = db_conn.query(exist_sql, param=exist_prm, fetchone=True, keep_conn=True)[0]
    if exist_obj:
        sql = "UPDATE report_daily_message SET message=%s WHERE id=%s;"
        prm = (msg_item.message, exist_obj['id'])
    else:
        sql = "INSERT INTO report_daily_message (report_date,variety_en,category_id,message) VALUES (%s,%s,%s,%s);"
        prm = (msg_item.integer_report_date(), msg_item.variety_en, msg_item.category_id, msg_item.message)
    _, success = db_conn.execute(sql, prm)
    return AllResponse.operate_successfully(msg='保存成功！') if success else AllResponse.server_error()
